package com.pf.utils;

import com.pf.export.MergedCellDemo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @author PanFei
 * @version 1.0.0
 * @createTime 2023/3/4 20:44
 */
@Slf4j
public class ExportExcelUtils {

    /**
     * 创建单元格样式
     * <p>
     * 字体：11号，宋体，粗体
     * 文本超链接：超链接有下划线，蓝色
     * 对齐：文本内容自动换行
     * 边框：上下左右细边框
     *
     * @param workbook excel工作薄对象
     * @param isLink   文本是否带有超链接 true-有超链接 false-无超链接
     * @param isBold   是否粗体 true-粗体 false-非粗体
     * @return
     */
    private static XSSFCellStyle getBasicCellStyle(XSSFWorkbook workbook, boolean isLink, boolean isBold) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        // 字体：宋体，11号字
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        if (isLink) {
            // 超链接带有下划线，颜色为蓝色
            font.setUnderline(Font.U_SINGLE);
            XSSFColor xssfColor = new XSSFColor();
            byte[] blue = {(byte) 0, (byte) 0, (byte) 255};
            xssfColor.setRGB(blue);
            font.setColor(XSSFColor.toXSSFColor(xssfColor));
        }
        font.setBold(isBold);
        cellStyle.setFont(font);
        // 对齐：单元格内容自动换行
        cellStyle.setWrapText(true);
        // 边框：上下左右边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        return cellStyle;
    }

    /**
     * 获取数据行单元格样式
     * <p>
     * 字体：11号，宋体，粗体
     * 文本超链接：超链接有下划线，蓝色
     * 对齐：文本内容自动换行，水平居左，垂直居中
     * 边框：上下左右细边框
     * 单元格格式：文本格式
     *
     * @param workbook
     * @param isLink
     * @return
     */
    private static XSSFCellStyle getDataCellStyle(XSSFWorkbook workbook, boolean isLink) {
        XSSFCellStyle cellStyle = getBasicCellStyle(workbook, isLink, false);
        // 水平居左，垂直居中
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 单元格：文本格式
        XSSFDataFormat dataFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
        return cellStyle;
    }

    /**
     * 获取数据行单元格样式
     * <p>
     * 字体：11号，宋体，粗体
     * 对齐：文本内容自动换行，水平居左，垂直居中
     * 边框：上下左右细边框
     * 单元格格式：文本格式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle getDataCellStyle(XSSFWorkbook workbook) {
        return getDataCellStyle(workbook, false);
    }

    /**
     * 获取数据行单元格样式
     * <p>
     * 字体：11号，宋体
     * 对齐：文本内容自动换行，水平，垂直居中
     * 边框：上下左右细边框
     * 单元格格式：文本格式，蓝色背景
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle getHeadCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = getBasicCellStyle(workbook, false, true);
        // 水平,垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 单元格：文本格式
        XSSFDataFormat dataFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
        // 背景色：蓝色
        byte[] blueRgb = {(byte) 0, (byte) 178, (byte) 222};
        XSSFColor xssfColor = new XSSFColor(blueRgb);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(xssfColor);
        return cellStyle;
    }

    /**
     * 获取数据行单元格样式
     * <p>
     * 字体：11号，宋体
     * 文本超链接：超链接有下划线，蓝色
     * 对齐：文本内容自动换行，水平居左，垂直居中
     * 边框：上下左右细边框
     * 单元格格式：文本格式，背景色灰色
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle getDataLinkCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = getDataCellStyle(workbook, true);
        // 超链接单元格背景色为灰色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        return cellStyle;
    }

    /**
     * 获取数据行单元格样式
     * <p>
     * 字体：11号，宋体
     * 对齐：文本内容自动换行，水平居左，垂直居中
     * 边框：上下左右细边框
     * 单元格格式：文本格式，背景色灰色
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle getDataGreyCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = getDataCellStyle(workbook, false);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        return cellStyle;
    }

    /**
     * 为excel单元格设置超链接
     *
     * @param workbook
     * @param cell
     * @param url
     */
    public static void setCellLink(Workbook workbook, Cell cell, String url) {
        CreationHelper creationHelper = workbook.getCreationHelper();
        Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
        hyperlink.setAddress(url);
        cell.setHyperlink(hyperlink);
    }

    /**
     * 设置有限的下拉列表（选项总和超过255字符，excel会报错）
     * <p>
     * 默认只给0-100行某列设置下拉列表
     *
     * @param sheet
     * @param columnIndex
     * @param optionList
     */
    public static void createBoundedDropDownList(XSSFSheet sheet, int columnIndex, List<String> optionList) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        String[] optionArray = optionList.toArray(new String[optionList.size()]);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(optionArray);
        // 设置生效的起始行，终止行，起始列，终止列（默认为0-100行某列设置下拉筛选）
        CellRangeAddressList addressList = new CellRangeAddressList(0, 100, columnIndex, columnIndex);
        DataValidation validation = helper.createValidation(constraint, addressList);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        validation.setShowPromptBox(true);
        sheet.addValidationData(validation);
    }

    /**
     * 通过隐藏sheet页方式创建下拉列表（选项超过255字符不会报错）
     * <p>
     * 默认只给0-100行某列设置下拉列表
     *
     * @param workbook
     * @param businessSheetName
     * @param businessCellIndex
     * @param lookupSheetRowIndex
     * @param optionList
     */
    public static void createUnlimitedDropDownList(XSSFWorkbook workbook, String businessSheetName, int businessCellIndex, int lookupSheetRowIndex, List<String> optionList) {
        // 获取隐藏的sheet页（lookup），如果没有则创建
        String defaultHiddenSheetName = "lookup";
        XSSFSheet lookupSheet = workbook.getSheet(defaultHiddenSheetName);
        if (lookupSheet == null) {
            lookupSheet = workbook.createSheet(defaultHiddenSheetName);
            workbook.setSheetHidden(workbook.getSheetIndex(lookupSheet), true);
        }
        // 将下拉选项数据存储在隐藏sheet页指定行中
        XSSFRow row = lookupSheet.createRow(lookupSheetRowIndex);
        for (int i = 0; i < optionList.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(optionList.get(i));
        }

        // 下拉选项值从隐藏sheet页中获取，格式如：lookup!$A$1:$F$1（取隐藏sheet页lookup的第一行，A列至F列数据），由隐藏sheet页名称，起始列，起始行，结束列，结束行
        String endCellName = CellReference.convertNumToColString(optionList.size() - 1);
        int rowNum = lookupSheetRowIndex + 1;
        String formula = defaultHiddenSheetName + "!$A$" + rowNum + ":$" + endCellName + "$" + rowNum;
        System.out.println("formula=" + formula);
        XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula);
        // 给业务sheet页指定某列设置下拉列表，默认给0-100行设置：起始行，终止行，起始列，终止列
        CellRangeAddressList addressList = new CellRangeAddressList(0, 100, businessCellIndex, businessCellIndex);
        // 数据有效性
        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(lookupSheet);
        DataValidation validation = helper.createValidation(constraint, addressList);
        XSSFSheet businessSheet = workbook.getSheet(businessSheetName);
        businessSheet.addValidationData(validation);
    }

    /**
     * 创建名称管理器
     *
     * @param workbook               excel工作薄对象
     * @param lookupSheetRowIndex    隐藏sheet页存放下拉列表选项的行序号
     * @param dataMap                级联下拉列表选项数据
     * @param isSpecialSymbolsHandle 是否要考虑对名称管理器特殊符号处理  true-要考虑 false-不考虑
     */
    public static void createSheetNameManger(XSSFWorkbook workbook, int lookupSheetRowIndex, Map<String, List<String>> dataMap, boolean isSpecialSymbolsHandle) {
        // 获取隐藏的sheet页（lookup），如果没有则创建
        String defaultHiddenSheetName = "lookup";
        XSSFSheet lookupSheet = workbook.getSheet(defaultHiddenSheetName);
        if (lookupSheet == null) {
            lookupSheet = workbook.createSheet(defaultHiddenSheetName);
            workbook.setSheetHidden(workbook.getSheetIndex(lookupSheet), true);
        }

        Iterator<Map.Entry<String, List<String>>> iterator = dataMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, List<String>> entry = iterator.next();
            String key = entry.getKey();
            // 创建隐藏sheet页（lookup）指定行（lookupSheetRowIndex）第一列单元格值，值为dataMap的key值（也是excel名称管理器的名称值）
            XSSFRow row = lookupSheet.createRow(lookupSheetRowIndex);
            row.createCell(0).setCellValue(key);

            // 循环创建隐藏sheet页（lookup）指定行lookupSheetRowIndex）第二列包括以后的单元格值，值为dataMap的value值（也是excel名称管理器的引用的隐藏sheet页lookup的数据行位置）
            List<String> eachDataList = CollectionUtils.isNotEmpty(entry.getValue()) ? entry.getValue() : Lists.newArrayList();
            String startColumnAddress = "";
            String endColumnAddress = "";
            for (int i = 0; i < eachDataList.size(); i++) {
                XSSFCell cell = row.createCell(i + 1);
                cell.setCellValue(eachDataList.get(i));
                if (i == 0) {
                    startColumnAddress = cell.getAddress().toString();
                }
                if (i == eachDataList.size() - 1) {
                    endColumnAddress = cell.getAddress().toString();
                }
            }
            try {
                // 添加名称管理器
                XSSFName name = workbook.createName();
                // 因为名称管理器不能含有特殊符号，如 ,;@等等，所以为了避免报错率，可以将某些特殊符号处理一下
                if (isSpecialSymbolsHandle) {
                    key = regexReplace(key);
                }
                name.setNameName(key);
                String formula = defaultHiddenSheetName + "!$" + startColumnAddress + ":$" + endColumnAddress;
                name.setRefersToFormula(formula);
            } catch (Exception e) {
                log.error(">>>>> createSheetNameManger:名称管理器创建失败...", e);
            }
            lookupSheetRowIndex++;
        }
    }

    /**
     * 因为名称管理器中不能有一些特殊符号，如（）@：，等等，因此将其替换为_
     *
     * @param str
     * @return
     */
    private static String regexReplace(String str) {
        String regex = "[@:]";
        return str.replaceAll(regex, "_");
    }

    /**
     * 全角空格为12288，半角空格为32
     * 其他字符半角(33-126)与全角(65281-65374)的对应关系是：均相差65248
     * <p>
     * 将字符串中的全角字符转为半角
     *
     * @param src 要转换的包含全角的任意字符串
     * @return 转换之后的字符串
     */
    public static String toSemiangle(String src) {
        char[] c = src.toCharArray();
        for (int index = 0; index < c.length; index++) {
            if (c[index] == 12288) {// 全角空格
                c[index] = (char) 32;
            } else if (c[index] > 65280 && c[index] < 65375) {// 其他全角字符
                c[index] = (char) (c[index] - 65248);
            }
        }
        return String.valueOf(c);
    }

    /**
     * 设置联动的下拉列表
     *
     * @param workbook               excel工作薄对象
     * @param businessSheetName      业务sheet页名称
     * @param businessCellIndex      业务sheet页需要下拉列表的指定列序号
     * @param cellColumnText         下拉列表引用隐藏sheet页数据的公式中单元格引用内容，如 $A2
     * @param isSpecialSymbolsHandle 是否要考虑对名称管理器特殊符号处理  true-要考虑 false-不考虑
     */
    public static void createCascadeDropDownList(XSSFWorkbook workbook, String businessSheetName, int businessCellIndex, String cellColumnText, boolean isSpecialSymbolsHandle) {
        // 因为导出的excel有表头，所以firstRow必须从1开始，如果从0开始三级联动就会错位
        CellRangeAddressList addressList = new CellRangeAddressList(1, 100, businessCellIndex, businessCellIndex);
        String formula;
        if (isSpecialSymbolsHandle) {
            // 其中 CustomRegexReplace 函数是excel中自定义的VBA函数，主要是正则表达式替换操作。现在只是将@符号替换为_
            // excel全角转半角的函数是：ASC(A2)
            formula = "INDIRECT(CustomRegexReplace(" + cellColumnText + ",\"[@:]\",\"_\"))";
        } else {
            formula = "INDIRECT(" + cellColumnText + ")";
        }

        XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula);
        // 获取默认的隐藏sheet页 lookup
        String defaultHiddenSheetName = "lookup";
        XSSFSheet lookupSheet = workbook.getSheet(defaultHiddenSheetName);
        if (lookupSheet == null) {
            log.error(">>>>> createCascadeDropDownList：excel中无lookup隐藏sheet页...");
            throw new RuntimeException("excel中无lookup隐藏sheet页");
        }
        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(lookupSheet);
        DataValidation validation = helper.createValidation(constraint, addressList);
        // 为业务sheet设置下拉列表
        XSSFSheet businessSheet = workbook.getSheet(businessSheetName);
        businessSheet.addValidationData(validation);
    }

    /**
     * 合并某列的哪些行数据
     * <p>
     * 一般实际需求中数据行的合并都是合并的某列的哪些行的数据（如合并A列的第1-10行数据），并不会说合并某行的哪些列数据（因为某类就代表某个字段，不同字段的值合并在一起没什么意义，也没必要）
     *
     * @param sheet
     * @param studentScoreList
     * @param mergeRowBeginIndex
     * @param mergeColumnIndexArray
     */
    public static void mergeRowCells(XSSFSheet sheet, List<MergedCellDemo.StudentScore> studentScoreList, int mergeRowBeginIndex, int... mergeColumnIndexArray) {
        String[] eachRowCellValueArray = new String[studentScoreList.size()];
        // 循环要合并的列
        // 循环取出某类所有数据行的单元格值
        for (int mergeColumnIndex : mergeColumnIndexArray) {
            int rowIndex = mergeRowBeginIndex;
            // 循环所有行
            for (int i = 0; i < studentScoreList.size(); i++) {
                XSSFRow row = sheet.getRow(rowIndex);
                XSSFCell cell = row.getCell(mergeColumnIndex);
                String cellValue = "";
                // 根据单元格类型获取单元格值还需再优化
                if (CellType.NUMERIC.equals(cell.getCellType())) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } else if (CellType.STRING.equals(cell.getCellType())) {
                    cellValue = cell.getStringCellValue();
                }
                eachRowCellValueArray[i] = cellValue;
                rowIndex++;
            }

            // 判断某列所有行数据，并合并行
            int index = 0; // index为计数器
            for (int i = 0; i < eachRowCellValueArray.length - 1; i++) {
                String currCellValue = eachRowCellValueArray[i];
                String nextCellValue = eachRowCellValueArray[i + 1];
                index++; //1 2 3
                //判断单元格值是否相同
                if (!currCellValue.equals(nextCellValue)) {
                    if (1 - index != 0) { //如果只有一个单元格，不合并
                        CellRangeAddress cellAddresses = new CellRangeAddress(i + 1 - index + mergeRowBeginIndex, i + mergeRowBeginIndex, mergeColumnIndex, mergeColumnIndex);
                        sheet.addMergedRegion(cellAddresses);
                        index = 0;
                    }

                }
                //判断是否循环到最后一行
                if (i + 1 == eachRowCellValueArray.length - 1) {
                    if (index != 0) {//如果只有一个单元格，不合并
                        CellRangeAddress cellAddresses = new CellRangeAddress(i + 1 - index + mergeRowBeginIndex, i + 1 + mergeRowBeginIndex, mergeColumnIndex, mergeColumnIndex);
                        sheet.addMergedRegion(cellAddresses);
                    }
                }
            }
        }
    }
}
